# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import pymysql


class MySQLPipeline:
    def __init__(self, host, database, user, password):
        self.host = host
        self.database = database
        self.user = user
        self.password = password

    @classmethod
    # 获取配置文件中的MYSQL信息
    def from_crawler(cls, crawler):
        return cls(
            host=crawler.settings.get('MYSQL_HOST'),
            database=crawler.settings.get('MYSQL_DATABASE'),
            user=crawler.settings.get('MYSQL_USER'),
            password=crawler.settings.get('MYSQL_PASSWORD')
        )

    # 声明连接与游标
    def open_spider(self, spider):
        self.conn = pymysql.connect(
            host=self.host,
            user=self.user,
            password=self.password,
            database=self.database,
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )
        self.cursor = self.conn.cursor()
        self.create_table()

    def create_table(self):
        create_table_sql = """
        CREATE TABLE IF NOT EXISTS books (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            author VARCHAR(100) DEFAULT '未知',
            publisher VARCHAR(100) DEFAULT '未知',
            price DECIMAL(10, 2) DEFAULT NULL,
            tags VARCHAR(255) DEFAULT '未知',
            url VARCHAR(255) NOT NULL,
            fav_count INT DEFAULT 0,
            commend_count INT DEFAULT 0,
            summary TEXT
        ) 
        """
        self.cursor.execute(create_table_sql)
        self.conn.commit()

    # 数据插入表
    def process_item(self, item, spider):
        insert_sql = """
        INSERT INTO books (title, author, publisher, price, tags, url, fav_count, commend_count, summary)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        # 处理价格转换
        price = float(item['price']) if item['price'] != "未知" and item['price'].replace('.', '').isdigit() else None

        self.cursor.execute(insert_sql, (
            item['title'],
            item['author'],
            item['publisher'],
            price,
            item['tags'],
            item['url'],
            int(item['fav_count']),
            int(item['commend_count']),
            item['summary']
        ))
        self.conn.commit()
        return item

    def close_spider(self, spider):
        self.conn.close()






class BsPipeline:
    def process_item(self, item, spider):
        return item